﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.SqlClient

Public Class ScrumrData

	Dim _db As Database
	Dim _cmd As DbCommand

	Public Function SaveProject(ByVal sProjectName As String, ByVal sProjectDescription As String, ByVal sScrumMasterid As String, ByVal sProjectOwnerId As String) As Integer

        _db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_CreateProject")
			.AddInParameter(_cmd, "@projectName", DbType.String, sProjectName)
			.AddInParameter(_cmd, "@projectDesc", DbType.String, sProjectDescription)
			.AddInParameter(_cmd, "@smUserID", DbType.String, sScrumMasterid)
			.AddInParameter(_cmd, "@pmUserID", DbType.String, sProjectOwnerId)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try


	End Function

	Public Function SaveFeature(ByVal sProjectID As Integer, ByVal sFeatureName As String, ByVal sFeatureStory As String, ByVal iFeaturePriority As Integer) As Integer

		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_CreateFeature")
			.AddInParameter(_cmd, "@projectID", DbType.String, sProjectID)
			.AddInParameter(_cmd, "@featName", DbType.String, sFeatureName)
			.AddInParameter(_cmd, "@featStory", DbType.String, sFeatureStory)
			.AddInParameter(_cmd, "@featPriority", DbType.String, iFeaturePriority)
		End With

		Try
			Return _db.ExecuteNonQuery(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function SaveTask(ByVal sFeatureID As String, ByVal sTaskName As String, ByVal sTaskDesc As String, ByVal sPriorityID As String, ByVal sEstTime As String, ByVal sUserID As String) As Integer

		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_CreateTask")
			.AddInParameter(_cmd, "@featureID", DbType.String, sFeatureID)
			.AddInParameter(_cmd, "@taskName", DbType.String, sTaskName)
			.AddInParameter(_cmd, "@taskDesc", DbType.String, sTaskDesc)
			.AddInParameter(_cmd, "@priorityID", DbType.String, sPriorityID)
			.AddInParameter(_cmd, "@estTime", DbType.String, sEstTime)
			.AddInParameter(_cmd, "@userID", DbType.String, sUserID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Sub UpdateTask(ByVal iTaskId As Integer, ByVal sTaskName As String, ByVal sTaskDesc As String, ByVal iStatusId As Integer, ByVal iPriorityID As Integer, ByVal sEstTime As String, ByVal sActualTime As String, ByVal sUserID As String)

		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_UpdateTask")
			.AddInParameter(_cmd, "@taskId", DbType.Int32, iTaskId)
			.AddInParameter(_cmd, "@taskName", DbType.String, sTaskName)
			.AddInParameter(_cmd, "@taskDesc", DbType.String, sTaskDesc)
			.AddInParameter(_cmd, "@priorityID", DbType.Int32, iPriorityID)
			.AddInParameter(_cmd, "@statusID", DbType.Int32, iStatusId)
			.AddInParameter(_cmd, "@estTime", DbType.String, sEstTime)
			.AddInParameter(_cmd, "@ActualTime", DbType.String, sActualTime)
			.AddInParameter(_cmd, "@userID", DbType.String, sUserID)
		End With

		Try
			_db.ExecuteNonQuery(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Sub

	Public Function SaveSprint(ByVal sStartDate As String, ByVal sEndDate As String) As String


		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_CreateSprint")
			.AddInParameter(_cmd, "@startDate", DbType.String, sStartDate)
			.AddInParameter(_cmd, "@endDate", DbType.String, sEndDate)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function FillProjectOwnerName(ByVal iProjectID As Integer) As String


		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetProjectOwner")
			.AddInParameter(_cmd, "@projectID", DbType.String, iProjectID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function FillScrumMasterName(ByVal iProjectID As Integer) As String


		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetScrumMaster")
			.AddInParameter(_cmd, "@projectID", DbType.String, iProjectID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function SumOfFeatureHours(ByVal iFeatureID As Integer) As String


		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetFeatureHours")
			.AddInParameter(_cmd, "@featureID", DbType.String, iFeatureID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function CountOfFeatureTasks(ByVal iFeatureID As Integer) As String


		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetFeatureSubtaskCount")
			.AddInParameter(_cmd, "@featureID", DbType.String, iFeatureID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function DevHoursForSprint(ByVal iCount As Integer, ByVal sStartDate As String, ByVal sEndDate As String) As String


		_db = DatabaseFactory.CreateDatabase()

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetTotalDevHoursForSprint")
			.AddInParameter(_cmd, "@devCount", DbType.String, iCount)
			.AddInParameter(_cmd, "@start", DbType.String, sStartDate)
			.AddInParameter(_cmd, "@end", DbType.String, sEndDate)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetProjectList() As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetProjects")
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetProjectListForSMUserID(ByVal userID As String) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetProjectsForSMUserID")
			.AddInParameter(_cmd, "@UserID", DbType.String, userID)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetPrioritiesList() As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetPriorities")
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetFeaturesListBox(ByVal sProjectID As String) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetFeatures")
			.AddInParameter(_cmd, "@projectID", DbType.String, sProjectID)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetFeaturesList(ByVal sProjectID As Integer) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetFeatures")
			.AddInParameter(_cmd, "@projectID", DbType.String, sProjectID)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetFeaturesListBox(ByVal iProjectID As Integer) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetFeatures")
			.AddInParameter(_cmd, "@projectID", DbType.String, iProjectID)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetScrumMastersList() As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetUsersInScrumMasterRole")
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetProjectOwnersList() As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetUsersInProjectOwnerRole")
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetDevTeamList() As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetUsersInDevTeamRole")
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function AddDevTeamMemberToSprint(ByVal userID As String, ByVal iSprintID As Integer) As Integer
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_CreateSprintDevTeam")
			.AddInParameter(_cmd, "@sprintID", DbType.String, iSprintID)
			.AddInParameter(_cmd, "@userID", DbType.String, userID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function AddFeatureToSprint(ByVal iFeatureID As Integer, ByVal iSprintID As Integer) As Integer
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_CreateSprintFeature")
			.AddInParameter(_cmd, "@sprintID", DbType.String, iSprintID)
			.AddInParameter(_cmd, "@featureID", DbType.String, iFeatureID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetSubtasksForFeature(ByVal iFeatureID As Integer) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetFeatureSubtasks")
			.AddInParameter(_cmd, "@FeatureID", DbType.String, iFeatureID)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetFeaturesInSprint(ByVal iSprintId As Integer) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetFeaturesInSprint")
			.AddInParameter(_cmd, "@SprintId", DbType.Int32, iSprintId)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetUnclaimedTasks(ByVal iFeatureID As Integer) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetUnclaimedTasks")
			.AddInParameter(_cmd, "@featureID", DbType.String, iFeatureID)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function AssignTaskToUser(ByVal sUserID As String, ByVal iTaskID As Integer) As String
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_AssignTaskToUser")
			.AddInParameter(_cmd, "@userID", DbType.String, sUserID)
			.AddInParameter(_cmd, "@taskID", DbType.String, iTaskID)
		End With

		Try
			Return _db.ExecuteScalar(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

    End Function

	Public Function GetTaskDetails(ByVal iTaskId As Integer) As DataSet

		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetTaskDetails")
			.AddInParameter(_cmd, "@TaskId", DbType.Int32, iTaskId)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

    Public Function GetActiveSprints() As DataSet
        _db = DatabaseFactory.CreateDatabase

        With _db
            _cmd = .GetStoredProcCommand("scrumr_GetActiveSprints")
        End With

        Try
            Return _db.ExecuteDataSet(_cmd)
        Catch ex As Exception
            Throw New Exception("Data Layer error: " & ex.Message)
        End Try

    End Function

    Public Function GetSprintFeatures(ByVal iSprintID As Integer) As DataSet
        _db = DatabaseFactory.CreateDatabase

        With _db
            _cmd = .GetStoredProcCommand("scrumr_GetSprintFeatures")
            .AddInParameter(_cmd, "@SprintID", DbType.String, iSprintID)
        End With

        Try
            Return _db.ExecuteDataSet(_cmd)
        Catch ex As Exception
            Throw New Exception("Data Layer error: " & ex.Message)
        End Try

    End Function

    Public Function GetSubtasksForFeatureInSprint(ByVal iFeatureID As Integer) As DataSet
        _db = DatabaseFactory.CreateDatabase

        With _db
            _cmd = .GetStoredProcCommand("scrumr_GetSubtasksForFeatureInSprint")
            .AddInParameter(_cmd, "@FeatureID", DbType.String, iFeatureID)
        End With

        Try
            Return _db.ExecuteDataSet(_cmd)
        Catch ex As Exception
            Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function FillSprintsForUser(ByVal sUserId As String) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetSprintsForUser")
			.AddInParameter(_cmd, "@UserId", DbType.String, sUserId)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetTasksForUser(ByVal sUserId As String) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetTasksForUser")
			.AddInParameter(_cmd, "@UserId", DbType.String, sUserId)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Public Function GetStatuses(ByVal iStatusType As Integer) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetStatuses")
			.AddInParameter(_cmd, "@StatusTypeId", DbType.Int32, iStatusType)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

	Function GetAlternateTeamMembers(ByVal iTaskId As Integer) As DataSet
		_db = DatabaseFactory.CreateDatabase

		With _db
			_cmd = .GetStoredProcCommand("scrumr_GetSprintDevTeamMembersFromTask")
			.AddInParameter(_cmd, "@TaskId", DbType.Int32, iTaskId)
		End With

		Try
			Return _db.ExecuteDataSet(_cmd)
		Catch ex As Exception
			Throw New Exception("Data Layer error: " & ex.Message)
		End Try

	End Function

End Class
